package com.xff.dao;


import com.xff.bean.PositionBean;
import com.xff.bean.SeatBean;
import com.xff.dbUtils.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class SeatDao {


    public void updateAllSeatStatus(int roomid, int tip){
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "update seat set status=? where roomid=?";
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setInt(1,tip);
            stm.setInt(2,roomid);
            stm.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void updateOneSeatStatus(int seatid){
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "update seat set status=0 where seatid=?";
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setInt(1,seatid);

            stm.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void updateSeatStatus(int roomid,int seatrow,int seatcolumn,int status){
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "update seat set status=? where roomid=? AND seatrow=? and seatcolumn=?";
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setInt(1,status);
            stm.setInt(2,roomid);
            stm.setInt(3,seatrow);
            stm.setInt(4,seatcolumn);
            stm.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void updateSeatStatus(int roomid,String[] rowlist,String[] columnlist){
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "update seat set status=? where seatrow=? and seatcolumn=? AND roomid=?";
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);
            for(int i=0;i<rowlist.length;i++){
                stm.setInt(1,1);
                stm.setInt(2,Integer.parseInt(rowlist[i]));
                stm.setInt(3,Integer.parseInt(columnlist[i]));
                stm.setInt(4,roomid);
                stm.addBatch();
            }
            stm.executeBatch();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public int[] SelectStatus(int roomid){

        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "select status from seat where roomid="+roomid;
        int status[]=new int [72];
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
           for(int i=0;rs.next();i++){
                status[i]=rs.getInt("status");
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return status;
    }

    /*
    * 根据状态查询座位
    * */
    public ArrayList<SeatBean> SelectStatus(int roomid,int status){
        ArrayList<SeatBean> seatList=new ArrayList<>();
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "select * from seat where roomid="+roomid+" AND status="+status;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            while(rs.next()){
                SeatBean seatBean=new SeatBean();
                seatBean.setSeatid(rs.getInt("seatid"));
                seatBean.setRoomid(rs.getInt("roomid"));
                seatBean.setSeatrow(rs.getInt("seatrow"));
                seatBean.setSeatcolumn(rs.getInt("seatcolumn"));
                seatBean.setStatus(rs.getInt("status"));
                seatList.add(seatBean);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return seatList;
    }

    public ArrayList<SeatBean> SelectStatus(int roomid, int status1,int status2){
        ArrayList<SeatBean> seatList=new ArrayList<>();
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "select * from seat where roomid="+roomid+" AND (status="+status2+" OR status="+status1+")";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();
            while(rs.next()){
                SeatBean seatBean=new SeatBean();
                seatBean.setSeatid(rs.getInt("seatid"));
                seatBean.setRoomid(rs.getInt("roomid"));
                seatBean.setSeatrow(rs.getInt("seatrow"));
                seatBean.setSeatcolumn(rs.getInt("seatcolumn"));
                seatBean.setStatus(rs.getInt("status"));
                seatList.add(seatBean);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return seatList;
    }

    public SeatBean SelectSeat(int roomid, int seatrow,int seatcolumn){
        SeatBean seatBean=new SeatBean();;
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "select * from seat where roomid=? and seatrow=? and seatcolumn=?";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setInt(1,roomid);
            stm.setInt(2,seatrow);
            stm.setInt(3,seatcolumn);
            rs = stm.executeQuery();
            while(rs.next()){
                seatBean.setSeatid(rs.getInt("seatid"));
                seatBean.setRoomid(rs.getInt("roomid"));
                seatBean.setSeatrow(rs.getInt("seatrow"));
                seatBean.setSeatcolumn(rs.getInt("seatcolumn"));
                seatBean.setStatus(rs.getInt("status"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return seatBean;
    }

    public SeatBean SelectSeat(int seatid){
        SeatBean seatBean=new SeatBean();;
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "select * from seat where seatid=? ";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setInt(1,seatid);
            rs = stm.executeQuery();
            while(rs.next()){
                seatBean.setSeatid(rs.getInt("seatid"));
                seatBean.setRoomid(rs.getInt("roomid"));
                seatBean.setSeatrow(rs.getInt("seatrow"));
                seatBean.setSeatcolumn(rs.getInt("seatcolumn"));
                seatBean.setStatus(rs.getInt("status"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return seatBean;
    }

    public int SelectSeatid(int positionid){
        int a=0;
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "select seatid from myposition where positionid=? ";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setInt(1,positionid);
            rs = stm.executeQuery();
            while(rs.next()){
                a=rs.getInt("seatid");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return a;
    }

    /*
    * 插入一条position表记录
    * */
    public void addPosition(PositionBean positionBean){
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql="insert into myposition(seatid,aid,username,name,begintime,endtime,status) values(?,?,?,?,?,?,?)";

        try {
            PreparedStatement stm=conn.prepareStatement(sql);
            stm.setInt(1,positionBean.getSeatid());
            stm.setInt(2,positionBean.getAid());
            stm.setString(3,positionBean.getUsername());
            stm.setString(4,positionBean.getName());
            stm.setString(5,positionBean.getBegintime());
            stm.setString(6,positionBean.getEndtime());
            stm.setInt(7,positionBean.getStatus());

            stm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public PositionBean SelectMyPosition(String username,int status1,int status2){

        PositionBean positionBean=new PositionBean();;
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "select * from myposition where (status=? OR status=?)AND username=?";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setInt(1,status1);
            stm.setInt(2,status2);
            stm.setString(3,username);
            rs = stm.executeQuery();
            while(rs.next()){
               positionBean.setPositionid(rs.getInt("positionid"));
                positionBean.setSeatid(rs.getInt("seatid"));
                positionBean.setAid(rs.getInt("aid"));
                positionBean.setUsername(rs.getString("username"));
                positionBean.setName(rs.getString("name"));
                positionBean.setBegintime(rs.getString("begintime"));
                positionBean.setEndtime(rs.getString("endtime"));
                positionBean.setStatus(rs.getInt("status"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbUtil.CloseDB(rs, stm, conn);
        }
        return positionBean;
    }

    public void deletePosition(int positionid) {
        Connection conn = DbUtil.getConn();
        String sql = "delete from myposition where positionid=?";
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setInt(1, positionid);
            stm.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void updatePositionStatus(int positionid,String time,int tip){
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "";
        if(tip==1){//签到
           sql = "update myposition set begintime=?,status=2 where positionid=?";
        }
        if(tip==2){//签退
            sql = "update myposition set endtime=?,status=0 where positionid=?";
        }
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setString(1,time);
            stm.setInt(2,positionid);
            stm.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void updateAllPositionStatus(int status){
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "update myposition set status=?";
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.setInt(1,status);
            stm.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void updateSeatChooseable(){
        DbUtil dbUtil=new DbUtil();
        Connection conn = dbUtil.getConn();
        String sql = "update seat set status=0 where status=2";
        PreparedStatement stm = null;
        try {
            stm = conn.prepareStatement(sql);
            stm.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}
